using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.OracleClient;
//using ByteFX.Data.MySqlClient;

namespace Toenda.Foundation.Data {
	/// <summary>
	/// Class SimpleDAL
	/// </summary>
	public class SimpleDAL {
		private IDbConnection _idbcon;
		//private SqlConnection _mssqlcon;
		private DALSettings _dbcfg;
		private string _connection;
		private int _commandtimeout;

		// ---------------------------------------------------
		// CONSTRUCTORS
		// ---------------------------------------------------

		/// <summary>
		/// Specialized Ctor
		/// </summary>
		public SimpleDAL(DALSettings dbConfig) {
			this._dbcfg = dbConfig;
			this._connection = dbConfig.ConnectionString;
			this._commandtimeout = -1;
		}

		// ---------------------------------------------------
		// PROPERTIES
		// ---------------------------------------------------

		/// <summary>
		/// Set the command timeout
		/// </summary>
		public int CommandTimeout {
			get { return this._commandtimeout; }
			set { this._commandtimeout = value; }
		}

		/// <summary>
		/// Set the command timeout
		/// </summary>
		public int CommandTimeoutInSeconds {
			get { return (this._commandtimeout / 1000); }
			set { this._commandtimeout = (value * 1000); }
		}

		// ---------------------------------------------------
		// PUBLIC MEMBERS
		// ---------------------------------------------------

		/// <summary>
		/// Open a connection
		/// </summary>
		/// <returns></returns>
		public bool OpenConnection() {
			/* Open the connection only if the dal is not already connected. */
			if(this._idbcon != null 
			&& this._idbcon.State == ConnectionState.Open) {
				return false;
			}

			try {
				DbProviderFactory providerFactory = DbProviderFactories.GetFactory(_dbcfg.ProviderName);

				IDbConnection newCon = providerFactory.CreateConnection();
				newCon.ConnectionString = this._connection;
				newCon.Open();

				this._idbcon = newCon;

				return true;
			}
			catch(Exception exception) {
				throw new Exception(
					"Error on connecting to the database server!",
					exception
				);
			}
		}

		/// <summary>
		/// Close the connection
		/// </summary>
		public void CloseConnection() {
			if(this._idbcon != null && this._idbcon.State != ConnectionState.Closed) {
				//this._mssqlcon.Close();
				this._idbcon.Close();
			}
		}

		/// <summary>Creates a command object for the current provider type.</summary>
		/// <returns></returns>
		public DbCommand CreateCommand() {
			DbProviderFactory providerFactory = DbProviderFactories.GetFactory(_dbcfg.ProviderName);

			return providerFactory.CreateCommand();
		}

		/// <summary>
		/// Execute a non-query query
		/// </summary>
		/// <param name="query"></param>
		/// <returns></returns>
		public int ExecuteNonQuery(string query) {
			int num = 0;

			this.OpenConnection();
			//this._query = query;

			if(this._idbcon.State == ConnectionState.Open) {
				IDbCommand cmd = this._idbcon.CreateCommand();
				cmd.CommandText = query;
				num = cmd.ExecuteNonQuery();

				this.CloseConnection();
			}

			return num;
		}

		/// <summary>Executes a statement.</summary>
		/// <param name="cmd">The command to execute</param>
		/// <returns>The number of records affected</returns>
		public int ExecuteNonQuery(IDbCommand cmd) {
			if(cmd == null) {
				throw new ArgumentNullException("cmd");
			}

			bool isNewConnection = this.OpenConnection();

			/* Remember the old state. */
			IDbConnection oldConnection = cmd.Connection;
			int oldTimeout = cmd.CommandTimeout;

			/* Change settings and execute. */
			if(this._commandtimeout > 0) {
				cmd.CommandTimeout = this._commandtimeout;
			}
			cmd.Connection = this._idbcon;
			int ret = cmd.ExecuteNonQuery();

			/* Restore the old state. */
			cmd.CommandTimeout = oldTimeout;
			cmd.Connection = oldConnection;

			/* Close the connection, if it was used for this call only. */
			if(isNewConnection) {
				this.CloseConnection();
			}

			return ret;
		}

		/// <summary>
		/// Execute a query
		/// </summary>
		/// <param name="query"></param>
		/// <returns></returns>
		public DataSet ExecuteQuery(string query) {
			return this.ExecuteQuery(query, CommandType.Text);
		}

		/// <summary>
		/// Execute a query
		/// </summary>
		/// <param name="query"></param>
		/// <param name="cmdtype"></param>
		/// <returns></returns>
		public DataSet ExecuteQuery(string query, CommandType cmdtype) {
			this.OpenConnection();
			//this._query = query;

			DataSet data = new DataSet();

			if(this._idbcon.State != ConnectionState.Open) {
				throw new InvalidOperationException("Database connection not available.");
			}

			if(this._idbcon.State == ConnectionState.Open) {
				IDbCommand cmd = this._idbcon.CreateCommand();

				if(this._commandtimeout > 0) {
					cmd.CommandTimeout = this._commandtimeout;
				}

				cmd.CommandText = query;
				cmd.CommandType = cmdtype;

				IDbDataAdapter adp = this._GetDataAdapter(cmd);
				//this._GetDataAdapter(cmd).Fill(data);
				int ret = adp.Fill(data);

				this.CloseConnection();
			}

			return data;
		}

		/// <summary>
		/// Executes the query.
		/// </summary>
		/// <param name="cmd">The CMD.</param>
		/// <returns></returns>
		public DataSet ExecuteQuery(IDbCommand cmd) {
			this.OpenConnection();
			//this._query = query;

			DataSet data = new DataSet();

			if(this._idbcon.State != ConnectionState.Open) {
				throw new InvalidOperationException("Database connection not available.");
			}

			if(this._idbcon.State == ConnectionState.Open) {
				if(this._commandtimeout > 0) {
					cmd.CommandTimeout = this._commandtimeout;
				}
				cmd.Connection = this._idbcon;

				IDbDataAdapter adp = this._GetDataAdapter(cmd);
				int ret = adp.Fill(data);

				this.CloseConnection();
			}

			return data;
		}

		/// <summary>Executes a command.</summary>
		/// <param name="cmd">The command to execute</param>
		/// <returns>An IDataReader for the resultset of the executed command.</returns>
		public IDataReader ExecuteQueryForDataReader(IDbCommand cmd) {
			IDataReader reader = null;

			if(this._idbcon.State != ConnectionState.Open) {
				throw new InvalidOperationException("Database connection not available.");
			}

			if(this._commandtimeout > 0) {
				cmd.CommandTimeout = this._commandtimeout;
			}
			cmd.Connection = this._idbcon;
			reader = cmd.ExecuteReader();

			return reader;
		}

		/// <summary>
		/// Execute a query (connection must be manually open and close)
		/// </summary>
		/// <param name="query"></param>
		/// <param name="cmdtype"></param>
		/// <returns></returns>
		public IDataReader ExecuteQueryForDataReader(string query, CommandType cmdtype) {
			IDataReader reader = null;

			if(this._idbcon.State == ConnectionState.Open) {
				IDbCommand cmd = this._idbcon.CreateCommand();

				if(this._commandtimeout > 0) {
					cmd.CommandTimeout = this._commandtimeout;
				}

				cmd.CommandText = query;
				cmd.CommandType = cmdtype;

				reader = cmd.ExecuteReader();
			}

			return reader;
		}

		/// <summary>
		/// Executes the query for data table reader.
		/// </summary>
		/// <param name="cmd">The CMD.</param>
		/// <returns></returns>
		public DataTableReader ExecuteQueryForDataTableReader(IDbCommand cmd) {
			this.OpenConnection();

			DataTableReader reader = null;

			if(this._idbcon.State == ConnectionState.Open) {
				if(this._commandtimeout > 0) {
					cmd.CommandTimeout = this._commandtimeout;
				}
				cmd.Connection = this._idbcon;

				IDbDataAdapter adp = this._GetDataAdapter(cmd);
				DataSet data = new DataSet();
				int ret = adp.Fill(data);

				if(data != null) {
					reader = data.CreateDataReader();
				}

				this.CloseConnection();
			}

			return reader;
		}

		/// <summary>
		/// Execute a query
		/// </summary>
		/// <param name="query"></param>
		/// <param name="cmdtype"></param>
		/// <returns></returns>
		public DataTableReader ExecuteQueryForDataTableReader(string query, CommandType cmdtype) {
			this.OpenConnection();
			//this._query = query;

			DataTableReader reader = null;

			if(this._idbcon.State == ConnectionState.Open) {
				IDbCommand cmd = this._idbcon.CreateCommand();

				if(this._commandtimeout > 0) {
					cmd.CommandTimeout = this._commandtimeout;
				}

				cmd.CommandText = query;
				cmd.CommandType = cmdtype;

				reader = (DataTableReader)cmd.ExecuteReader();
				//IDataReader idr = cmd.ExecuteReader();
				//reader = idr.R

				this.CloseConnection();
			}

			return reader;
		}

		/// <summary>Executes a query for a single value.</summary>
		/// <param name="cmd">The command to execute.</param>
		/// <returns></returns>
		public object ExecuteScalar(IDbCommand cmd) {
			object result;

			bool isNewConnection = this.OpenConnection();

			if(this._commandtimeout > 0) {
				cmd.CommandTimeout = this._commandtimeout;
			}
			cmd.Connection = this._idbcon;
			result = cmd.ExecuteScalar();

			if(isNewConnection) {
				this.CloseConnection();
			}

			return result;
		}

		/// <summary>Executes a query for a single value.</summary>
		/// <param name="query">The query to execute.</param>
		/// <param name="cmdType">Type of the command.</param>
		/// <returns>The first cell in the first row of the resultset.</returns>
		public object ExecuteScalar(string query, CommandType cmdType) {
			this.OpenConnection();
			IDbCommand cmd = this._idbcon.CreateCommand();

			if(this._commandtimeout > 0) {
				cmd.CommandTimeout = this._commandtimeout;
			}
			cmd.CommandText = query;
			cmd.CommandType = cmdType;

			object result = cmd.ExecuteScalar();

			this.CloseConnection();

			return result;
		}

		/// <summary>
		/// Execute a update
		/// </summary>
		/// <param name="data"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		public int ExecuteUpdate(DataSet data, string table) {
			int num = 0;

			this.OpenConnection();

			if(this._idbcon.State == ConnectionState.Open) {
				DbProviderFactory provFactory = DbProviderFactories.GetFactory(this._dbcfg.ProviderName);

				DbCommand cmdSelect = provFactory.CreateCommand();
				cmdSelect.CommandText = "SELECT * FROM " + table;
				cmdSelect.Connection = (DbConnection)this._idbcon;

				DbDataAdapter adp = provFactory.CreateDataAdapter();
				adp.SelectCommand = cmdSelect;

				DbCommandBuilder cmdBuilder = provFactory.CreateCommandBuilder();
				cmdBuilder.DataAdapter = adp;

				adp.UpdateCommand = cmdBuilder.GetUpdateCommand();

				num = adp.Update(data);

				this.CloseConnection();
			}

			return num;
		}

		// ---------------------------------------------------
		// PROTECTED MEMBERS
		// ---------------------------------------------------

		// ---------------------------------------------------
		// PRIVATE MEMBERS
		// ---------------------------------------------------

		/// <summary>
		/// Get a data adapter from a command
		/// </summary>
		/// <param name="cmd"></param>
		/// <returns></returns>
		private IDbDataAdapter _GetDataAdapter(IDbCommand cmd) {
			DbProviderFactory provFactory = DbProviderFactories.GetFactory(this._dbcfg.ProviderName);
			IDbDataAdapter adp = provFactory.CreateDataAdapter();

			adp.SelectCommand = cmd;

			return adp;
		}
	}
}
